﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.SqlClient;
using MDL;

namespace DAL
{
    public class DAL_Tables
    {
        /// <summary>
        /// 根据房间ID获取该房间所有餐桌信息
        /// </summary>
        /// <param name="Rtid">房间ID</param>
        /// <returns>该房间的所有餐桌信息</returns>
        public static List<Tables> SelectTables(int Rtid)
        {
            string sql = "select * from Tables where RTID = @RTID";
            SqlParameter[] sprs = { new SqlParameter("@RTID", Rtid) };
            List<Tables> list = new List<Tables>();
            using (SqlDataReader dr = DBHelper.白道(sql, sprs, CommandType.Text))
            {
                while (dr.Read())
                {
                    Tables tb = new Tables();
                    tb.TableID = Convert.ToInt32(dr[0]);
                    tb.TableName = dr[1].ToString();
                    tb.RTID = Convert.ToInt32(dr[2]);
                    tb.TableArea = dr[3].ToString();
                    tb.TableState = Convert.ToInt32(dr[4]);
                    list.Add(tb);
                }
            }
            return list;
        }
        /// <summary>
        /// 根据餐桌状态显示房间的餐桌
        /// </summary>
        /// <param name="TableState">根据餐桌状态</param>
        /// <returns>需要显示的餐桌ID</returns>
        public static List<Tables> SelectTable(int RTID, int TableState)
        {
            string sql = "select * from Tables where RTID = @RTID and TableState = @TableState";
            SqlParameter[] sprs = { 
                                   new SqlParameter("@RTID",RTID),
                                   new SqlParameter("@TableState",TableState)
                                   };
            List<Tables> list = new List<Tables>();
            using (SqlDataReader dr = DBHelper.白道(sql, sprs, CommandType.Text))
            {
                while (dr.Read())
                {
                    Tables t = new Tables();
                    t.TableID = Convert.ToInt32(dr[0]);
                    t.TableName = dr[1].ToString();
                    list.Add(t);
                }
            }
            return list;
        }
        /// <summary>
        /// 根据餐桌ID和状态查找所要显示数据
        /// </summary>
        /// <param name="tb"></param>
        /// <returns></returns>
        public static List<Tables> select_Tables_Data(Tables tb)
        {
            string sql = @"select * from Tables t inner join RoomType r on t.RTID=r.RTID where 1=1";
            List<SqlParameter> list = new List<SqlParameter>();
            if (!string.IsNullOrEmpty(tb.RTID.ToString()))
            {
                sql += " and t.RTID=@ID";
                list.Add(new SqlParameter("@ID", tb.RTID));
            }
            if (!string.IsNullOrEmpty(tb.TableState.ToString()))
            {
                sql += " and t.TableState=@state";
                list.Add(new SqlParameter("@state", tb.TableState));
            }
            if (!string.IsNullOrEmpty(tb.TableName))
            {
                sql += " and TableName=@name";
                list.Add(new SqlParameter("@name", tb.TableName));
            }
            SqlDataReader sr = DBHelper.白道(sql, list.ToArray(), CommandType.Text);
            List<Tables> T = new List<Tables>();
            while (sr.Read())
            {
                Tables t = new Tables();
                t.TableID = Convert.ToInt32(sr["TableID"]);
                t.RTID = Convert.ToInt32(sr["RTID"]);
                t.TableName = sr["TableName"].ToString();
                t.TableArea = sr["TableArea"].ToString();
                t.TableState = Convert.ToInt32(sr["TableState"]);
                t.RTName = sr["RTName"].ToString();
                t.RTConsume = float.Parse(sr["RTConsume"].ToString());
                T.Add(t);
            }
            return T;
        }
        /// <summary>
        /// 根据餐桌ID和状态查找所有数据
        /// </summary>
        /// <param name="tb"></param>
        /// <returns></returns>
        public static List<Tables> select_Tables(Tables tb)
        {
            string sql = @"select * from Tables t inner join RoomType r on t.RTID=r.RTID where t.TableState between 0 and 3 ";
            List<SqlParameter> list = new List<SqlParameter>();
            if (!string.IsNullOrEmpty(tb.RTID.ToString()))
            {
                sql += " and t.RTID=@ID";
                list.Add(new SqlParameter("@ID", tb.RTID));
            }
            if (!string.IsNullOrEmpty(tb.TableName))
            {
                sql += " and TableName=@name";
                list.Add(new SqlParameter("@name", tb.TableName));
            }
            SqlDataReader sr = DBHelper.白道(sql, list.ToArray(), CommandType.Text);
            List<Tables> T = new List<Tables>();
            while (sr.Read())
            {
                Tables t = new Tables();
                t.TableID = Convert.ToInt32(sr["TableID"]);
                t.RTID = Convert.ToInt32(sr["RTID"]);
                t.TableName = sr["TableName"].ToString();
                t.TableArea = sr["TableArea"].ToString();
                t.TableState = Convert.ToInt32(sr["TableState"]);
                t.RTName = sr["RTName"].ToString();
                t.RTConsume = float.Parse(sr["RTConsume"].ToString());
                T.Add(t);
            }
            return T;
        }
        /// <summary>
        /// 根据餐桌ID修改餐桌当前状态
        /// </summary>
        /// <param name="TableId">餐桌ID</param>
        /// <param name="TableState">目标状态</param>
        /// <returns>受影响的行数</returns>
        public static int UpdateTableState(int TableId, int TableState)
        {
            string sql = "update Tables set TableState=@TableState where TableID=@TableID";
            SqlParameter[] sprs = { 
                                      new SqlParameter("@TableID",TableId),
                                      new SqlParameter("@TableState",TableState)
                                  };
            return DBHelper.增删改(sql, sprs, CommandType.Text);
        }
        /// <summary>
        /// 统计餐桌状态数量
        /// </summary>
        /// <param name="tablestate">根据的状态</param>
        /// <returns>每个状态的数量</returns>
        public static object SelectTableState(int tablestate)
        {
            string sql = "select COUNT(*) from Tables";
            List<SqlParameter> sprs = new List<SqlParameter>();
            if (tablestate < 4)
            {
                sql = "select COUNT(*) from Tables where TableState = @tablestate";
                sprs.Add(new SqlParameter("@tablestate", tablestate));
            }
            return DBHelper.单值(sql, sprs.ToArray(), CommandType.Text);
        }
        /// <summary>
        /// 根据餐桌名字查找是否可用
        /// </summary>
        /// <param name="tb"></param>
        /// <returns></returns>
        public static List<Tables> select_Tables_state(Tables tb)
        {
            string sql = @"select * from Tables t inner join RoomType r on t.RTID=r.RTID where 1=1 ";
            List<SqlParameter> list = new List<SqlParameter>();
            if (!string.IsNullOrEmpty(tb.TableName))
            {
                sql += " and TableName=@name";
                list.Add(new SqlParameter("@name", tb.TableName));
            }
            SqlDataReader sr = DBHelper.白道(sql, list.ToArray(), CommandType.Text);
            List<Tables> T = new List<Tables>();
            while (sr.Read())
            {
                Tables t = new Tables();
                t.TableID = Convert.ToInt32(sr["TableID"]);
                t.RTID = Convert.ToInt32(sr["RTID"]);
                t.TableName = sr["TableName"].ToString();
                t.TableArea = sr["TableArea"].ToString();
                t.TableState = Convert.ToInt32(sr["TableState"]);
                t.RTName = sr["RTName"].ToString();
                t.RTConsume = float.Parse(sr["RTConsume"].ToString());
                T.Add(t);
            }
            return T;
        }
        /// <summary>
        /// 查询消费餐桌人数
        /// </summary>
        /// <param name="ID">餐桌ID</param>
        /// <param name="Zdh">账单编号</param>
        /// <returns>返回人数</returns>
        public static object SelectTablesNum(int ID, string Zdh)
        {
            string sql = @"select CBAmount from Tables t inner join ConsumerBill c on t.TableID=c.TableID
                            where t.TableID = @ID and CBID = @Zdh";
            SqlParameter[] sprs = { 
                                   new SqlParameter("@ID",ID),
                                   new SqlParameter("@Zdh",Zdh)
                                   };
            return DBHelper.单值(sql, sprs, CommandType.Text);
        }
        /// <summary>
        /// 查询餐台信息
        /// </summary>
        /// <param name="RID">根据房间类型ID</param>
        /// <returns>餐台信息</returns>
        public static List<Tables> SelectTableInfo(int RID)
        {
            string sql = "select * from Tables t inner join RoomType r on t.RTID=r.RTID where 1=1 ";
            List<SqlParameter> sprs = new List<SqlParameter>();
            if (RID > 0)
            {
                sql += " and T.RTID = @RID";
                sprs.Add(new SqlParameter("@RID", RID));
            }
            List<Tables> list = new List<Tables>();
            using (SqlDataReader dr = DBHelper.白道(sql, sprs.ToArray(), CommandType.Text))
            {
                while (dr.Read())
                {
                    Tables t = new Tables();
                    t.TableName = dr[1].ToString();
                    t.RTName = dr[6].ToString();
                    t.TableState = Convert.ToInt32(dr[4]);
                    t.TableArea = dr[3].ToString();
                    list.Add(t);
                }
            }
            return list;
        }
        /// <summary>
        /// 下拉绑定房间区域
        /// </summary>
        /// <returns>房间区域</returns>
        public static List<Tables> SelectTableQuYu()
        {
            string sql = "select TableArea from Tables group by TableArea";
            List<Tables> list = new List<Tables>();
            using (SqlDataReader dr = DBHelper.白道(sql, null, CommandType.Text))
            {
                while (dr.Read())
                {
                    Tables t = new Tables();
                    t.TableArea = dr[0].ToString();
                    list.Add(t);
                }
            }
            return list;
        }
        /// <summary>
        /// 单个增加餐台
        /// </summary>
        /// <param name="name">餐台名称</param>
        /// <param name="id">餐台所属房间类型</param>
        /// <param name="qy">所在区域</param>
        /// <param name="state">餐台状态</param>
        /// <returns>受影响的行数</returns>
        public static int AddTable(string name, int id, string qy, int state)
        {
            string sql = "insert into tables values(@TName,@RTID,@Tqy,@Tstate)";
            SqlParameter[] sprs = { 
                                  new SqlParameter("@TName",name),
                                  new SqlParameter("@RTID",id),
                                  new SqlParameter("@Tqy",qy),
                                  new SqlParameter("@Tstate",state)
                                  };
            return DBHelper.增删改(sql, sprs, CommandType.Text);
        }
        /// <summary>
        /// 删除餐台
        /// </summary>
        /// <param name="name">根据餐台名称</param>
        /// <returns>受影响的行数</returns>
        public static int DeleteTable(string name)
        {
            string sql = "delete Tables where TableName = @name";
            SqlParameter[] sprs = { new SqlParameter("@name", name) };
            return DBHelper.增删改(sql, sprs, CommandType.Text);
        }
        /// <summary>
        /// 修改餐台信息
        /// </summary>
        /// <param name="name">餐台名称</param>
        /// <param name="id">所属房间类型</param>
        /// <param name="qy">所在区域</param>
        /// <param name="name1">根据餐台名称</param>
        /// <returns>受影响的行数</returns>
        public static int UpdateTable(string name, int id, string qy, string name1)
        {
            string sql = "update Tables set TableName=@name,RTID=@id,TableArea=@qy where TableName = @name1";
            SqlParameter[] sprs = { 
                                  new SqlParameter("@name",name),
                                  new SqlParameter("@id",id),
                                  new SqlParameter("@qy",qy),
                                  new SqlParameter("@name1",name1)
                                  };
            return DBHelper.增删改(sql, sprs, CommandType.Text);
        }
        /// <summary>
        /// 根据餐台ID查询是否可预定的餐台
        /// </summary>
        /// <param name="state">餐台状态</param>
        /// <returns>是否可预定的餐台</returns>
        public static List<Tables> GetTablesState(int state)
        {
            string sql = "select * from Tables where TableState = @state";
            List<Tables> list = new List<Tables>();
            SqlParameter[] sprs = { new SqlParameter("@state", state) };
            using (SqlDataReader dr = DBHelper.白道(sql, sprs, CommandType.Text))
            {
                while (dr.Read())
                {
                    Tables t = new Tables();
                    t.TableID = Convert.ToInt32(dr[0]);
                    t.TableName = dr[1].ToString();
                    t.TableState = Convert.ToInt32(dr[4]);
                    list.Add(t);
                }
            }
            return list;
        }
        /// <summary>
        /// 根据餐桌名称修改餐桌当前状态
        /// </summary>
        /// <param name="TableId">餐桌名称</param>
        /// <param name="TableState">目标状态</param>
        /// <returns>受影响的行数</returns>
        public static int UpdateTableState(string name, int State)
        {
            string sql = "update Tables set TableState=@TableState where TableName=@TableName";
            SqlParameter[] sprs = { 
                                      new SqlParameter("@TableName",name),
                                      new SqlParameter("@TableState",State)
                                  };
            return DBHelper.增删改(sql, sprs, CommandType.Text);
        }
    }
}
